package com.github;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.IterableUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import lombok.NonNull;

/**
 * 
 * @author Justin
 * 
 */
@SuppressWarnings("deprecation")
public class ExcelReader {

	private Workbook workbook;

	private int titleRow;

	private int beginRow;

	private int endRow;

	private int beginColumn;

	/**
	 * 
	 * @param builder
	 */
	private ExcelReader(Builder builder) {

		this.titleRow = builder.aTitleRow;
		this.beginRow = builder.aBeginRow;
		this.endRow = builder.aEndRow;
		this.beginColumn = builder.aBeginColumn;

		try {
			this.workbook = builder.workbookProvider.provide();
		} catch (IOException e) {
			throw new WorkbookCreationException("Error Occured On Creating Workbook", e);
		}
	}

	/**
	 * 根据表格名称获取内容
	 * 
	 * @param <T>
	 * @param rowBeanClass
	 * @param sheetName
	 * @return
	 */
	public <T> List<T> read(Class<T> rowBeanClass, final String sheetName) {
		return read(rowBeanClass, () -> workbook.getSheet(sheetName));
	}

	/**
	 * 
	 * 
	 * 从传入的表格获取内容
	 * 
	 * @param rowBeanClass
	 * @param sheet
	 * @return
	 */
	public <T> List<T> read(Class<T> rowBeanClass, final Sheet sheet) {
		return read(rowBeanClass, () -> sheet);
	}

	/**
	 * 根据表格的index获取内容
	 * 
	 * @param rowBeanClass
	 * @param sheetIndex
	 * @return
	 */
	public <T> List<T> read(Class<T> rowBeanClass, final int sheetIndex) {
		return read(rowBeanClass, () -> workbook.getSheetAt(sheetIndex));
	}

	/**
	 * 获取表的内容
	 * 
	 * @param rowBeanClass
	 * @param sheetProvider
	 * @return
	 */
	public <T> List<T> read(Class<T> rowBeanClass, SheetProvider sheetProvider) {
		Sheet sheet = sheetProvider.provide();
		// 如果没有数据（表格里的最后一行都没有达到beginRow）就返回空列表。
		if (sheet.getLastRowNum() < beginRow)
			return Collections.emptyList();
		final int sheetLastRowNum = sheet.getLastRowNum() + 1; // 因为最后是exclusive，所以应该加一。
		final int actualEndRow = (sheetLastRowNum < endRow) ? sheetLastRowNum : endRow;

		List<Title> titles = titleMapping(sheet);
		List<Field> markedFields = markedFields(rowBeanClass);
		try {
			List<T> list = new ArrayList<>();
			for (int i = beginRow; i < actualEndRow; i++) {

				final Row row = sheet.getRow(i);
				// 如果是标题行或结果为空则跳过当前行
				if (i == titleRow || row == null || isEmptyRow(row))
					continue;
				T bean = rowBeanClass.newInstance();

				populateBean(bean, row, titles, markedFields);
				list.add(bean);
			}
			return list;
		} catch (InstantiationException | IllegalAccessException e) {
			throw new ExcelException("Exception Occurred On Initializing rowBeanClass", e);
		}
	}

	/**
	 * 将cell对应的内容set到bean里面去 目前只支持int String double BigDecimal几种类型
	 * 
	 * @param bean
	 * @param cell
	 * @param titles
	 * @return
	 * @throws ExcelException
	 */
	private void populateBean(Object bean, Row row, final List<Title> titles, final List<Field> markedFields)
			throws IllegalAccessException {
		if (CollectionUtils.isNotEmpty(markedFields)) {
			for (final Field field : markedFields) {
				populateBean(bean, row, field, titles);
			}
		} // 为空就不管了
	}

	/**
	 * 填充单个Field
	 * 
	 * @param bean
	 * @param row
	 * @param field
	 * @param titles
	 * @throws IllegalAccessException
	 */
	private void populateBean(Object bean, Row row, Field field, final List<Title> titles)
			throws IllegalAccessException {
		CellAt anno = field.getAnnotation(CellAt.class);
		if (anno != null) {
			final String fieldTitle = anno.title();
			// 如果设定优先使用index或fieldTitle为空，则使用index。否则优先使用Title。
			int fieldIndex = anno.useIndexPrior() || StringUtils.isBlank(fieldTitle) ? anno.index()
					: indexByTitle(fieldTitle, titles);
			if (fieldIndex < 0) // 找不到标题对应的index就继续。不进行转换。
				return;
			Cell cell = row.getCell(beginColumn + fieldIndex);

			field.setAccessible(true);
			if (field.getType() == String.class) {
				field.set(bean, WorkbookCellUtils.asString(cell));
			} else if (field.getType() == BigDecimal.class) {
				field.set(bean, WorkbookCellUtils.asBigDecimal(cell));
			} else if (DataTypeUtils.isInteger(field.getType())) {
				field.set(bean, WorkbookCellUtils.asInteger(cell));
			} else if (field.getType() == double.class || field.getType() == Double.class) {
				field.set(bean, WorkbookCellUtils.asDouble(cell));
			} else if (field.getType() == boolean.class || field.getType() == Boolean.class) {
				field.set(bean, WorkbookCellUtils.asBoolean(cell));
			} else
				// 其他情况抛出【不支持操作】异常
				throw new ExcelException(String.format("Exception Occurred On Populating Bean: {CellType: %s, %s}",
						cell.getCellType(), cell));
		}
	}

	/**
	 * 检查一行是否为空
	 * 
	 * @param row
	 * @return
	 */
	private boolean isEmptyRow(Row row) {
		if (row == null) {
			return true;
		}
		if (row.getLastCellNum() <= 0) {
			return true;
		}
		boolean isEmptyRow = true;
		for (int cellNum = row.getFirstCellNum(); cellNum < row.getLastCellNum(); cellNum++) {
			Cell cell = row.getCell(cellNum);
			if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && StringUtils.isNotBlank(cell.toString())) {
				isEmptyRow = false;
			}
		}
		return isEmptyRow;
	}

	/**
	 * 根据titleText获取index
	 * 
	 * @param titleText
	 * @param titles
	 * @return
	 */
	private int indexByTitle(final String titleText, List<Title> titles) {
		Title title = IterableUtils.find(titles, (Title object) -> {
			if (StringUtils.equals(titleText, object.getTitleText()))
				return true;
			return false;
		});

		if (title == null)
			return -1;
		return title.getTitleIndex();
	}

	/**
	 * 获取title行的mapping
	 * 
	 * @param sheet
	 * @return
	 */
	private List<Title> titleMapping(Sheet sheet) {
		if (sheet.getLastRowNum() < titleRow)
			return Collections.emptyList();

		List<Title> list = new ArrayList<>();
		Row row = sheet.getRow(titleRow);

		if (row == null)// 如果没有找到标题行则返回空列表。
			return Collections.emptyList();

		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			Cell cell = row.getCell(i);
			if (cell == null) // 存在lastCellNum不等于获取的表格不为null。
				continue;
			Title title = new Title(cell.getStringCellValue(), i);
			list.add(title);
		}
		return list;
	}

	/**
	 * 获取被标记的域，这些域将会被用于对应填充属性。
	 * 
	 * @param <T>
	 * 
	 * @return
	 */
	private <T> List<Field> markedFields(Class<T> rowBeanClass) {
		Field[] fields = rowBeanClass.getDeclaredFields();
		List<Field> list = new ArrayList<>();
		for (Field field : fields) {
			if (field.isAnnotationPresent(CellAt.class)) {
				list.add(field);
			}
		}
		return list;
	}

	/**
	 * @param is
	 * @return
	 */
	public static Builder builder(InputStream is) {
		return builder(() -> new XSSFWorkbook(is));
	}

	/**
	 * @param workbookProvider
	 * @return
	 */
	public static Builder builder(WorkbookProvider workbookProvider) {
		return new Builder(workbookProvider);
	}

	/**
	 * 
	 * @author Justin
	 *
	 * @param <E>
	 */
	public static class Builder {
		private int aBeginColumn;
		private int aTitleRow;
		private int aBeginRow;
		private int aEndRow;
		private WorkbookProvider workbookProvider;

		/**
		 * 
		 * @param fileStream
		 * @param sheetName
		 * @param rowBeanClass
		 */
		public Builder(WorkbookProvider workbookProvider) {
			this.aTitleRow = 0;
			this.aBeginRow = 1;
			this.aEndRow = 1;
			this.aBeginColumn = 0;
			// 默认使用XSSWorkbook
			this.workbookProvider = workbookProvider;
		}

		/**
		 * 标题行
		 * 
		 * @param rowNumber
		 * @return
		 */
		public Builder title(int rowNumber) {
			this.aTitleRow = rowNumber;
			return this;
		}

		/**
		 * 从哪一行开始(inclusive)
		 * 
		 * @param rowNumber
		 * @return
		 */
		public Builder beginRow(int rowNumber) {
			this.aBeginRow = rowNumber;
			return this;
		}

		/**
		 * 设定最大取到哪一行(Exclusive)
		 * 
		 * @param rowNumber
		 * @return
		 */
		public Builder endRow(int rowNumber) {
			this.aEndRow = rowNumber;
			return this;
		}

		public Builder beginColumn(int beginColumn) {
			this.aBeginColumn = beginColumn;
			return this;
		}

		public Builder workbookProvider(@NonNull WorkbookProvider workbookProvider) {
			this.workbookProvider = workbookProvider;
			return this;
		}

		/**
		 * 
		 * @return
		 */
		public ExcelReader build() {
			if (this.aBeginRow < 0 || (this.aBeginRow > this.aEndRow) || this.aTitleRow < 0
					|| this.workbookProvider == null)
				throw new IllegalArgumentException();
			return new ExcelReader(this);
		}
	}
}
